package com.webapp.model;

import java.sql.DriverManager;
import java.sql.ResultSet;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;

public class ViewApplicationModel {

	private static Connection conn = null;
	private static String driver = "com.mysql.jdbc.Driver";
	private static String dbUrl = "jdbc:mysql://mysql1.cs.stonybrook.edu:3306/gdonnelly";
	private static String dbPassword = "106338300";
	private static String dbUser = "gdonnelly";
	
	private static Application app;
	
	/*
	 * Performs queries from all tables and gets the
	 * data needed for an entire Application object.
	 * 
	 * Returns a the Application object.
	 */
	public static Application getApplication(String email){
		app = new Application();
		app.setEmail(email);
		
		try{
			Statement stmt = connect();
			ResultSet rs;
			
			
			/*
			 * gets: app id, first name, middle name, last name, birth month, birth day, birth year, gender,
			 * ethnicity, race, citizenship, perm. rsident, phone number
			 */
			rs = stmt.executeQuery("select * from ay_applicant_general_info where applicant_email = '" +email+ "'");
			rs.next();
			
			app.setAppId(rs.getInt(2));
			app.setFirstName(rs.getString(3));
			app.setLastName(rs.getString(4));
			app.setMiddleName(rs.getString(5));
			app.setBirthMonth(rs.getInt(6));
			app.setBirthDay(rs.getInt(7));
			app.setBirthYear(rs.getInt(8));
			app.setGender(rs.getString(9));
			app.setEthnicity(rs.getString(10));
			app.setRace(rs.getString(11));
			app.setCitizenship(rs.getString(12));
			app.setpPermanentResident(rs.getString(13));
			app.setPhone(rs.getInt(14));
			
			/*
			 * gets: SBU GPA, SBU ID#, degree program, upload date, tier, summary, TOEFL comments,
			 * number desired reviews, average rating
			 */
			rs = stmt.executeQuery("select * from app_general_info where applicant_email = '" +email+ "'");
			rs.next();
			
			app.setSbuGpa(rs.getDouble(3));
			app.setSbuId(rs.getInt(4));
			app.setDegreeProgram(rs.getString(5));
			app.setUploadDate(rs.getString(6));
			app.setTier(rs.getFloat(7));
			app.setSummary(rs.getString(8));
			app.setToeflComments(rs.getString(9));
			app.setNumDesiredReviews(rs.getInt(10));
			app.setAvgRating(rs.getFloat(11));
			
			/*
			 * gets: theory, algorithm, programming language, OS:
			 * 			title, scale, grade, SBU equiv.
			 */
			rs = stmt.executeQuery("select * from ay_applicant_courses where applicant_email = '" +email+"'");
			rs.next();
			
			app.setTheoryCouseTitle(rs.getString(3));
			app.setTheoryScale(rs.getDouble(4));
			app.setTheoryGrade(rs.getDouble(5));
			app.setTheorySbuEquiv(rs.getString(6));
			
			app.setAlgorithmCourseTitle(rs.getString(7));
			app.setAlgorithmScale(rs.getDouble(8));
			app.setAlgorithmGrade(rs.getDouble(9));
			app.setAlgorithmSbuEquiv(rs.getString(10));
			
			app.setProgLangCourseTitle(rs.getString(11));
			app.setProgLangScale(rs.getDouble(12));
			app.setProgLangGrade(rs.getDouble(13));
			app.setProgLangSbuEquiv(rs.getString(14));
			
			app.setOsCourseTitle(rs.getString(15));
			app.setOsScale(rs.getDouble(16));
			app.setOsGrade(rs.getDouble(17));
			app.setOsSbuEquiv(rs.getString(18));
			
			/*
			 * gets: research area, research topic
			 */
			rs = stmt.executeQuery("select * from ay_applicant_edu_background where applicant_email = '" +email+"'");
			rs.next();
			
			app.setResearchArea(rs.getString(3));
			app.setResearchTopic(rs.getString(4));
			
			/*
			 * gets: grad:
			 * 			inst, gpa, gpa scale, gpa1, gpa2, rank, rank out of
			 */
			rs = stmt.executeQuery("select * from ay_applicant_grad_insititution where applicant_email = '" +email+ "'");
			rs.next();
			
			app.setGradInst(rs.getString(3));
			app.setGradGpa(rs.getDouble(4));
			app.setGradScale(rs.getDouble(5));
			app.setGradGpa1(rs.getDouble(6));
			app.setGradGpa2(rs.getDouble(7));
			app.setGradRankint(rs.getInt(8));
			app.setGradRankOutOf(rs.getInt(9));
			
			/*
			 * gets: undergrad:
			 * 			inst, gpa, gpa scale, gpa1, gpa2, gpa3, gpa4, gpa5, rank, rank out of
			 */
			rs = stmt.executeQuery("select * from ay_applicant_ug_insititute where applicant_email = '" +email+ "'");
			rs.next();
			
			app.setUgInst(rs.getString(3));
			app.setUgGpa(rs.getDouble(4));
			app.setUgScale(rs.getDouble(5));
			app.setUgGpa1(rs.getDouble(6));
			app.setUgGpa2(rs.getDouble(7));
			app.setUgGpa3(rs.getDouble(8));
			app.setUgGpa4(rs.getDouble(9));
			app.setUgGpa5(rs.getDouble(10));
			app.setUgRank(rs.getInt(11));
			app.setUgRankOutOf(rs.getInt(12));
			
			/*
			 * gets: gre:
			 * 			score/percent: verb, qual, analy
			 * 			subject name, subject score
			 */
			rs = stmt.executeQuery("select * from ay_applicant_gre where applicant_email = '" +email+ "'");
			rs.next();
			
			app.setGreV(rs.getInt(3));
			app.setGreVPctile(rs.getInt(4));
			app.setGreQ(rs.getInt(5));
			app.setGreQPctile(rs.getInt(6));
			app.setGreA(rs.getInt(7));
			app.setGreAPctile(rs.getInt(8));
			app.setGreSubjName(rs.getString(9));
			app.setGreSubjScore(rs.getInt(10));
			
			/*
			 * gets: TOEFL Internet, IELTS Score
			 */
			rs = stmt.executeQuery("select * from ay_applicant_toefl where applicant_email = '" +email+ "'");
			rs.next();
			
			app.setToeflInternet(rs.getInt(3));
			app.setIelts(rs.getInt(4));
			
			/*
			 * gets: official gre (OTS):
			 * 			score/percent: verb, qual, analy
			 * 			subject name, subject score, subject percent
			 */
			rs = stmt.executeQuery("select * from ots_applicant_gre_score where applicant_email = '" +email+ "'");
			rs.next();
			
			app.setOfclGreV(rs.getInt(3));
			app.setOfclGreVPctile(rs.getInt(4));
			app.setOfclGreQ(rs.getInt(5));
			app.setOfclGreQPctile(rs.getInt(6));
			app.setOfcileGreA(rs.getInt(7));
			app.setOfclGreAPctile(rs.getInt(8));
			app.setOfclGreSubj(rs.getInt(9));
			app.setOfclGreAPctile(rs.getInt(10));
			app.setOfclGreSubjName(rs.getString(11));
			
			/*
			 * gets: TOEFL:
			 * 			total, listen, read, speak, write
			 */
			rs = stmt.executeQuery("select * from ots_applicant_toefl_scores where applicant_email = '" +email+ "'");
			rs.next();
			
			app.setOfclToeflTotal(rs.getInt(3));
			app.setOfclToeflListen(rs.getInt(4));
			app.setOfclToeflRead(rs.getInt(5));
			app.setOfclToeflSpeak(rs.getInt(6));
			app.setOfclToeflWrite(rs.getInt(7));
			
			rs = stmt.executeQuery("select * from app_reviews where applicant_email = '" +email+ "'");
			
			while(rs.next()){
				if(rs.getInt(3) == 1){
					app.setRating1(rs.getDouble(4));
					app.setReview1(rs.getString(5));
					app.setReviewDate1(rs.getString(6));
					app.setReviewer1(rs.getString(7));
				}
				else if(rs.getInt(3) == 2){
					app.setRating2(rs.getDouble(4));
					app.setReview2(rs.getString(5));
					app.setReviewDate2(rs.getString(6));
					app.setReviewer2(rs.getString(7));
				}
				else if(rs.getInt(3) == 3){
					app.setRating3(rs.getDouble(4));
					app.setReview3(rs.getString(5));
					app.setReviewDate3(rs.getString(6));
					app.setReviewer3(rs.getString(7));
				}
				else if(rs.getInt(3) == 4){
					app.setRating4(rs.getDouble(4));
					app.setReview4(rs.getString(5));
					app.setReviewDate4(rs.getString(6));
					app.setReviewer4(rs.getString(7));
				}
				else if(rs.getInt(3) == 5){
					app.setRating5(rs.getDouble(4));
					app.setReview5(rs.getString(5));
					app.setReviewDate5(rs.getString(6));
					app.setReviewer5(rs.getString(7));
				}
			}
			
			//close the connection
			rs.close();
			stmt.close();
			conn.close();
			
		}catch(Exception ex){
			//cannot connect to database
		}
		return app;
	}
	
	/*
	 * Performs queries to update the database with the Application object
	 * in the parameter. The Application will contain an email, used as a key for 
	 * all tables, and updates all values within the Application object,
	 * even if the values are the same. Performs the same data functions from the
	 * method getApplication(), except instead of getting this data, it puts this data.
	 * 
	 * NOTE: does not edit reviews. Does not allow for review edits.
	 * 
	 * Returns true if successful, false is unsuccessful.
	 */
	public static boolean editApplication(Application app){
		try{
			Statement stmt = connect();
			
			/*
			 * UPDATES: app id, first name, middle name, last name, birth month, birth day, birth year, gender
			 * 			ethnicity, race, citizenship, perm. resident, phone number
			 */
			stmt.executeUpdate("UPDATE ay_applicant_general_info SET applicant_ID = '" +app.getAppId()+
								"', applicant_first_name = '" +app.getFirstName()+
								"', applicant_last_name = '" +app.getLastName()+
								"', applicant_middle_name = '" +app.getMiddleName()+
								"', applicant_birth_month = '" +app.getBirthMonth()+
								"', applicant_birth_day = '" +app.getBirthDay()+
								"', applicant_birth_year = '" + app.getBirthYear()+
								"', applicant_gender = '" +app.getGender()+
								"', applicant_ethnicity = '" +app.getEthnicity()+
								"', applicant_race = '" +app.getRace()+
								"', applicant_citizenship = '" +app.getCitizenship()+
								"', applicant_perm_resident = '" +app.getpPermanentResident()+
								"', applicant_phone_num = '" +app.getPhone()+ "' WHERE applicant_email = '" +app.getEmail()+ "'");
			
			/*
			 * UPDATES: app id, SBU GPA, SBU ID#, degree program, upload date, tier, summary, TOEFL comments
			 * number desired reviews, average rating
			 */
			stmt.executeUpdate("UPDATE app_general_info SET applicant_ID = '" +app.getAppId()+
								"', applicant_SBU_ID = '" +app.getSbuId()+
								"', applicant_SBU_GPA = '" +app.getSbuGpa()+
								"', application_degree_program = '" +app.getDegreeProgram()+
								"', application_file_upload_date = '" +app.getUploadDate()+
								"', application_tier = '" +app.getTier()+
								"', application_summary = '" +app.getSummary()+
								"', application_TOELF_comments = '" +app.getToeflComments()+
								"', application_desired_reviews = '" +app.getNumDesiredReviews()+
								"', application_avg_rating = '" +app.getAvgRating()+ "' WHERE applicant_email = '" +app.getEmail()+ "'");
			
			/*
			 * UPDATES: All AY Course titles, grades, scales and SBU equivelent
			 */
			stmt.executeUpdate("UPDATE ay_applicant_courses SET applicant_ID = '" +app.getAppId()+
								"', course_theory = '" +app.getTheoryCouseTitle()+
								"', course_theory_grade_scale = '" +app.getTheoryScale()+
								"', course_theory_grade = '" +app.getTheoryGrade()+
								"', sbu_equivalent_theory = '" +app.getTheorySbuEquiv()+
								"', course_algo = '" +app.getAlgorithmCourseTitle()+
								"', course_algo_grade_scale = '" +app.getAlgorithmScale()+
								"', course_algo_grade = '" +app.getAlgorithmGrade()+
								"', sbu_equivalent_algo = '" +app.getAlgorithmSbuEquiv()+
								"', course_ppl_compilers = '" +app.getProgLangCourseTitle()+
								"', course_ppl_compilers_grade_scale = '" +app.getProgLangScale()+
								"', course_ppl_compilers_grade = '" +app.getProgLangGrade()+
								"', sbu_equivalent_ppl_compilers = '" +app.getProgLangSbuEquiv()+
								"', course_os = '" +app.getOsCourseTitle()+
								"', course_os_grade_scale = '" +app.getOsScale()+
								"', course_os_grade = '" +app.getOsGrade()+
								"', sbu_equivalent_os = '" +app.getOsSbuEquiv()+ "' WHERE applicant_email = '" +app.getEmail()+ "'");
			
			/*
			 * UPDATES: research ares, research topic, undergrad inst, grad inst
			 */
			stmt.executeUpdate("UPDATE ay_applicant_edu_background SET applicant_ID = '" +app.getAppId()+
								"', research_area = '" +app.getResearchArea()+
								"', research_topics = '" +app.getResearchTopic()+
								"', undergrad_institution = '" +app.getUgInst()+
								"', grad_institution = '" +app.getGradInst()+ "' WHERE applicant_email = '" +app.getEmail()+ "'");
			
			/*
			 * UPDATES: grad institution / GPA / GPA scale / GPA1 / GPA2 / rank / rank out of
			 */
			stmt.executeUpdate("UPDATE ay_applicant_grad_insititution SET applicant_ID = '" +app.getAppId()+
								"', grad_insititution = '" +app.getGradInst()+
								"', grad_gpa = '" +app.getGradGpa()+
								"', grad_scale = '" +app.getGradScale()+
								"', grad_year6_gpa = '" +app.getGradGpa1()+
								"', grad_year7_gpa = '" +app.getGradGpa2()+
								"', grad_rank = '" +app.getGradRank()+
								"', grad_rank_out_of = '" +app.getGradRankOutOf()+ "' WHERE applicant_email = '" +app.getEmail()+ "'");
			
			/*
			 * UPDATES: undergrad institution / GPA / GPA scale / GPA1 / GPA2 / GPA3 / GPA4 / GPA5 / rank / rand out of
			 */
			stmt.executeUpdate("UPDATE ay_applicant_ug_insititute SET applicant_ID = '" +app.getAppId()+
								"', ug_insititution = '" +app.getUgInst()+
								"', ug_gpa = '" +app.getUgGpa()+
								"', ug_scale = '" +app.getUgScale()+
								"', ug_year1_gpa = '" +app.getUgGpa1()+
								"', ug_year2_gpa = '" +app.getUgGpa2()+
								"', ug_year3_gpa = '" +app.getUgGpa3()+
								"', ug_year4_gpa = '" +app.getUgGpa4()+
								"', ug_year5_gpa = '" +app.getUgGpa5()+
								"', ug_rank = '" +app.getUgRank()+
								"', ug_rank_out_of = '" +app.getUgRankOutOf()+ "' WHERE applicant_email = '" +app.getEmail()+ "'");
			
			/*
			 * UPDATES: AY GRE scores
			 */
			stmt.executeUpdate("UPDATE ay_applicant_gre SET applicant_ID = '" +app.getAppId()+
								"', gre_verbal = '" +app.getGreV()+
								"', gre_verbal_percentile = '" +app.getGreVPctile()+
								"', gre_quant = '" +app.getGreQ()+
								"', gre_quant_percentile = '" +app.getGreQPctile()+
								"', gre_analy = '" +app.getGreA()+
								"', gre_analy_percentile = '" +app.getGreAPctile()+
								"', gre_test = '" +app.getGreSubjName()+
								"', gre_test_score = '" +app.getGreSubjScore()+ "' WHERE applicant_email = '" +app.getEmail()+ "'");
			
			/*
			 * UPDATES: TOEFL Internet, IELTS score
			 */
			stmt.executeUpdate("UPDATE ay_applicant_toefl SET applicant_ID = '" +app.getAppId()+
								"', TOELF_Internet = '" +app.getToeflInternet()+
								"', IELTS_score = '" +app.getIelts()+ "' WHERE applicant_email = '" +app.getEmail()+ "'");
			
			/*
			 * UPDATES: Official GRE scores
			 */
			stmt.executeUpdate("UPDATE ots_applicant_gre_score SET applicant_ID = '" +app.getAppId()+
								"', off_gre_v = '" +app.getOfclGreV()+
								"', off_gre_vp = '" +app.getOfclGreVPctile()+
								"', off_gre_q = '" +app.getOfclGreQ()+
								"', off_gre_qp = '" +app.getOfclGreQPctile()+
								"', off_gre_a = '" +app.getOfclGreA()+
								"', off_gre_ap = '" +app.getOfclGreAPctile()+
								"', off_gre_subj = '" +app.getOfclGreSubj()+
								"', off_gre_subjp = '" +app.getOfclGreSubjPctile()+
								"', off_gre_subjname = '" +app.getOfclGreSubjName()+ "' WHERE applicant_email = '" +app.getEmail()+ "'");
			
			/*
			 * UPDATES: official TOEFL scores
			 */
			stmt.executeUpdate("UPDATE ots_applicant_toefl_scores SET applicant_ID = '" +app.getAppId()+
								"', off_toelf_total = '" +app.getOfclToeflTotal()+
								"', off_toelf_lis = '" +app.getOfclToeflListen()+
								"', off_toelf_rd = '" +app.getOfclToeflRead()+
								"', off_toelf_spk = '" +app.getOfclToeflSpeak()+
								"', off_toelf_wri = '" +app.getOfclToeflWrite()+ "' WHERE applicant_email = '" +app.getEmail()+ "'");
			
			//close database connection
			stmt.close();
			conn.close();
			
			return true;
		}catch(Exception ex){
			return false;
			//cannot connect to database
		}
	}
	
	public static Statement connect(){
		try{
			Class.forName(driver);
			conn = (Connection) DriverManager.getConnection(dbUrl, dbUser, dbPassword);
			Statement stmt = (Statement) conn.createStatement();
			
			return stmt;
		}catch(Exception ex){return null;}
	}
}
